package thesis.database;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class CustomerJDBCTemplate implements CustomerDAO {

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplateObject;

	@Override
	public void setDataSource(DataSource ds) {
		this.dataSource = ds;
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);

	}

	@Override
	public void create(Customer cus) {
		String sql = "insert into Customer (customer_name"
				+ ",home_address,office_address,lastest_address,creation_time,"
				+ "last_update,home_phone,office_phone,newest_phone) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		jdbcTemplateObject.update(sql, cus.getCustomerName(),
				cus.getHomeAddress(), cus.getOfficeAddress(),
				cus.getNewestAddress(), cus.getCreationTime(),
				cus.getLastUpdate(), cus.getHomePhone(), cus.getOfficePhone(),
				cus.getNewestPhone());
		System.out.println("Created Customer Record");

	}

	@Override
	public int createAndReturnId(Customer cus) {
		final Customer data = cus;
		final String sql = "insert into customer(customer_name,home_address,office_address,lastest_address,creation_time,last_update,"
				+ "home_phone,office_phone,newest_phone) values "
				+ "(?, ?, ?, ?, ?, ?, ?, ?, ?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplateObject.update(new PreparedStatementCreator() {
			public java.sql.PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				java.sql.PreparedStatement ps = connection.prepareStatement(
						sql, new String[] { "id" });
				ps.setString(1, data.getCustomerName());
				ps.setString(2, data.getHomeAddress());
				ps.setString(3, data.getOfficeAddress());
				ps.setString(4, data.getNewestAddress());
				ps.setDate(5, new java.sql.Date(data.getCreationTime()
						.getTime()));
				ps.setDate(6, new java.sql.Date(data.getLastUpdate().getTime()));
				ps.setString(7, data.getHomePhone());
				ps.setString(8, data.getOfficePhone());
				ps.setString(9, data.getNewestPhone());

				return ps;
			}
		}, keyHolder);
		System.out.println("Created customer Record");

		return keyHolder.getKey().intValue();

	}

	@Override
	public Customer getCustomer(Integer id) {
		String sql = "select * from customer where customer_id = ? ";
		Customer customer = jdbcTemplateObject.queryForObject(sql,
				new Object[] { id }, new CustomerMapper());
		return customer;
	}

	@Override
	public List<Customer> listCustomers() {
		String sql = "select * from customer";
		List<Customer> list = jdbcTemplateObject.query(sql,
				new CustomerMapper());
		return list;
	}

	@Override
	public void delete(Integer id) {
		String sql = "delete from customer where customer_id = ?";
		jdbcTemplateObject.update(sql, id);
		System.out.println("Deleted Customer Record with ID = " + id);

	}

	@Override
	public int update(Customer cus) {
		String sql = "update  customer set customer_name = ? ,home_address = ? ,office_address = ? ,lastest_address = ?,creation_time = ?,"
				+ "last_update = ?,home_phone = ?,office_phone = ?,newest_phone = ? where customer_id = ?";

		System.out.println("Updated Customer Record with ID = "
				+ cus.getCustomerId());

		return jdbcTemplateObject.update(sql, cus.getCustomerName(),
				cus.getHomeAddress(), cus.getOfficeAddress(),
				cus.getNewestAddress(), cus.getCreationTime(),
				cus.getLastUpdate(), cus.getHomePhone(), cus.getOfficePhone(),
				cus.getNewestPhone(), cus.getCustomerId());

	}

	@Override
	public ArrayList<String> listPhoneNumbers(int id) {
		String sql = "select home_phone,office_phone,newest_phone from customer where customer_id = ?";
		jdbcTemplateObject.update(sql, id);
		return null;
	}

	@Override
	public Customer getCustomerByPhoneNumber(String phoneNumber) {
		Customer customer;
		String sql = "select * from customer where office_phone = ? or home_phone = ? or newest_phone = ? ";
		try {
			customer = jdbcTemplateObject.queryForObject(sql, new Object[] {
					phoneNumber, phoneNumber, phoneNumber },
					new CustomerMapper());
		} catch (DataAccessException dae) {

			System.out.println(getErrorFromException(dae.getMessage())
					+ phoneNumber);
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return customer;
	}

	@Override
	public List<Customer> getCusApproNumber(String phone) {
		List<Customer> customers = null;
		String sql = "select * from customer where office_phone like ? or home_phone like ? or newest_phone like ? ";

		try {
			customers = jdbcTemplateObject.query(sql, new CustomerMapper(), "%"
					+ phone + "%", "%" + phone + "%", "%" + phone + "%");
		} catch (DataAccessException dae) {
			System.out.println(getErrorFromException(dae.getMessage()));
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		return customers;
	}

	private String getErrorFromException(String msg) {
		// true : not exist, false : more than one
		char[] chars = msg.toCharArray();

		if (Integer.parseInt(String.valueOf(chars[msg.length() - 11])) < Integer
				.parseInt(String.valueOf(chars[msg.length() - 1]))) {
			return "There are more than one customer in database with phone number : ";
		} else
			return "Not exist any customer in database with phone number : ";

	}

	@Override
	public ArrayList<String> getAddressesById(int id) {
		String sql = "select * from customer where customer_id = ?";
		Customer customer = null;
		try {
			customer = jdbcTemplateObject.queryForObject(sql,
					new Object[] { id }, new CustomerMapper());
		} catch (DataAccessException dae) {

			System.out.println("This id is not exist !");
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		ArrayList<String> addresses = new ArrayList<String>();
		addresses.add(customer.getHomeAddress());
		addresses.add(customer.getOfficeAddress());
		addresses.add(customer.getNewestAddress());
		return addresses;
	}
}
